USE [master]
GO
IF DB_ID (N'SQLServer2005DB') IS NOT NULL
DROP DATABASE Archive;
GO
-- Get the SQL Server data path
DECLARE @data_path nvarchar(256);
SET @data_path = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
                  FROM master.sys.master_files
                  WHERE database_id = 1 AND file_id = 1);
                  
/****** Object:  Database [SQLServer2005DB]     ******/
EXECUTE ('CREATE DATABASE [SQLServer2005DB] ON  PRIMARY 
( NAME = N''SQLServer2005DB'', FILENAME = ''' + @data_path + N'SQLServer2005DB.mdf'' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N''SQLServer2005DB_Log'', FILENAME = ''' + @data_path + N'SQLServer2005DB.ldf'' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)')
GO

ALTER DATABASE [SQLServer2005DB] SET COMPATIBILITY_LEVEL = 90
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [SQLServer2005DB].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO

ALTER DATABASE [SQLServer2005DB] SET ANSI_NULL_DEFAULT OFF 
GO

ALTER DATABASE [SQLServer2005DB] SET ANSI_NULLS OFF 
GO

ALTER DATABASE [SQLServer2005DB] SET ANSI_PADDING OFF 
GO

ALTER DATABASE [SQLServer2005DB] SET ANSI_WARNINGS OFF 
GO

ALTER DATABASE [SQLServer2005DB] SET ARITHABORT OFF 
GO

ALTER DATABASE [SQLServer2005DB] SET AUTO_CLOSE OFF 
GO

ALTER DATABASE [SQLServer2005DB] SET AUTO_CREATE_STATISTICS ON 
GO

ALTER DATABASE [SQLServer2005DB] SET AUTO_SHRINK OFF 
GO

ALTER DATABASE [SQLServer2005DB] SET AUTO_UPDATE_STATISTICS ON 
GO

ALTER DATABASE [SQLServer2005DB] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO

ALTER DATABASE [SQLServer2005DB] SET CURSOR_DEFAULT  GLOBAL 
GO

ALTER DATABASE [SQLServer2005DB] SET CONCAT_NULL_YIELDS_NULL OFF 
GO

ALTER DATABASE [SQLServer2005DB] SET NUMERIC_ROUNDABORT OFF 
GO

ALTER DATABASE [SQLServer2005DB] SET QUOTED_IDENTIFIER OFF 
GO

ALTER DATABASE [SQLServer2005DB] SET RECURSIVE_TRIGGERS OFF 
GO

ALTER DATABASE [SQLServer2005DB] SET  DISABLE_BROKER 
GO

ALTER DATABASE [SQLServer2005DB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO

ALTER DATABASE [SQLServer2005DB] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO

ALTER DATABASE [SQLServer2005DB] SET TRUSTWORTHY OFF 
GO

ALTER DATABASE [SQLServer2005DB] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO

ALTER DATABASE [SQLServer2005DB] SET PARAMETERIZATION SIMPLE 
GO

ALTER DATABASE [SQLServer2005DB] SET READ_COMMITTED_SNAPSHOT OFF 
GO

ALTER DATABASE [SQLServer2005DB] SET HONOR_BROKER_PRIORITY OFF 
GO

ALTER DATABASE [SQLServer2005DB] SET  READ_WRITE 
GO

ALTER DATABASE [SQLServer2005DB] SET RECOVERY SIMPLE 
GO

ALTER DATABASE [SQLServer2005DB] SET  MULTI_USER 
GO

ALTER DATABASE [SQLServer2005DB] SET PAGE_VERIFY CHECKSUM  
GO

ALTER DATABASE [SQLServer2005DB] SET DB_CHAINING OFF 
GO

USE [SQLServer2005DB]
GO
/****** Object:  Table [dbo].[Person]     ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Person](
	[PersonID] [int] IDENTITY(1,1) NOT NULL,
	[LastName] [nvarchar](50) NOT NULL,
	[FirstName] [nvarchar](50) NOT NULL,
	[PersonCategory] [smallint] NOT NULL,
	[HireDate] [datetime] NULL,
	[EnrollmentDate] [datetime] NULL,
	[Picture] [image] NULL,
 CONSTRAINT [PK_School.Student] PRIMARY KEY CLUSTERED 
(
	[PersonID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Person] ON
INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [PersonCategory], [HireDate], [EnrollmentDate], [Picture]) VALUES (1, N'Abercrombie', N'Kim', 2, CAST(0x000087CF00000000 AS DateTime), NULL, NULL)
INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [PersonCategory], [HireDate], [EnrollmentDate], [Picture]) VALUES (2, N'Barzdukas', N'Gytis', 1, NULL, CAST(0x000096C200000000 AS DateTime), NULL)
INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [PersonCategory], [HireDate], [EnrollmentDate], [Picture]) VALUES (3, N'Justice', N'Peggy', 1, NULL, CAST(0x0000910D00000000 AS DateTime), NULL)
INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [PersonCategory], [HireDate], [EnrollmentDate], [Picture]) VALUES (4, N'Fakhouri', N'Fadi', 2, CAST(0x0000926000000000 AS DateTime), NULL, NULL)
INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [PersonCategory], [HireDate], [EnrollmentDate], [Picture]) VALUES (5, N'Harui', N'Roger', 2, CAST(0x00008C8700000000 AS DateTime), NULL, NULL)
INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [PersonCategory], [HireDate], [EnrollmentDate], [Picture]) VALUES (6, N'Li', N'Yan', 1, NULL, CAST(0x0000927A00000000 AS DateTime), NULL)
INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [PersonCategory], [HireDate], [EnrollmentDate], [Picture]) VALUES (7, N'Norman', N'Laura', 1, NULL, CAST(0x000093E700000000 AS DateTime), NULL)
INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [PersonCategory], [HireDate], [EnrollmentDate], [Picture]) VALUES (8, N'Olivotto', N'Nino', 1, NULL, CAST(0x000096C200000000 AS DateTime), NULL)
INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [PersonCategory], [HireDate], [EnrollmentDate], [Picture]) VALUES (9, N'Tang', N'Wayne', 1, NULL, CAST(0x000096C200000000 AS DateTime), NULL)
INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [PersonCategory], [HireDate], [EnrollmentDate], [Picture]) VALUES (10, N'Alonso', N'Meredith', 1, NULL, CAST(0x0000927A00000000 AS DateTime), NULL)
INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [PersonCategory], [HireDate], [EnrollmentDate], [Picture]) VALUES (11, N'Lopez', N'Sophia', 1, NULL, CAST(0x0000955500000000 AS DateTime), NULL)
INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [PersonCategory], [HireDate], [EnrollmentDate], [Picture]) VALUES (12, N'Browning', N'Meredith', 1, NULL, CAST(0x00008FA000000000 AS DateTime), NULL)
INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [PersonCategory], [HireDate], [EnrollmentDate], [Picture]) VALUES (13, N'Anand', N'Arturo', 1, NULL, CAST(0x000093E700000000 AS DateTime), NULL)
INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [PersonCategory], [HireDate], [EnrollmentDate], [Picture]) VALUES (14, N'Walker', N'Alexandra', 1, NULL, CAST(0x00008FA000000000 AS DateTime), NULL)
INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [PersonCategory], [HireDate], [EnrollmentDate], [Picture]) VALUES (15, N'Powell', N'Carson', 1, NULL, CAST(0x0000955500000000 AS DateTime), NULL)
INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [PersonCategory], [HireDate], [EnrollmentDate], [Picture]) VALUES (16, N'Jai', N'Damien', 1, NULL, CAST(0x0000910D00000000 AS DateTime), NULL)
INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [PersonCategory], [HireDate], [EnrollmentDate], [Picture]) VALUES (17, N'Carlson', N'Robyn', 1, NULL, CAST(0x000096C200000000 AS DateTime), NULL)
INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [PersonCategory], [HireDate], [EnrollmentDate], [Picture]) VALUES (18, N'Zheng', N'Roger', 2, CAST(0x0000948B00000000 AS DateTime), NULL, NULL)
INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [PersonCategory], [HireDate], [EnrollmentDate], [Picture]) VALUES (19, N'Bryant', N'Carson', 1, NULL, CAST(0x0000910D00000000 AS DateTime), NULL)
INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [PersonCategory], [HireDate], [EnrollmentDate], [Picture]) VALUES (20, N'Suarez', N'Robyn', 1, NULL, CAST(0x0000955500000000 AS DateTime), NULL)
INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [PersonCategory], [HireDate], [EnrollmentDate], [Picture]) VALUES (21, N'Holt', N'Roger', 1, NULL, CAST(0x0000955500000000 AS DateTime), NULL)
INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [PersonCategory], [HireDate], [EnrollmentDate], [Picture]) VALUES (22, N'Alexander', N'Carson', 1, NULL, CAST(0x000096C200000000 AS DateTime), NULL)
INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [PersonCategory], [HireDate], [EnrollmentDate], [Picture]) VALUES (23, N'Morgan', N'Isaiah', 1, NULL, CAST(0x0000910D00000000 AS DateTime), NULL)
INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [PersonCategory], [HireDate], [EnrollmentDate], [Picture]) VALUES (24, N'Martin', N'Randall', 1, NULL, CAST(0x000096C200000000 AS DateTime), NULL)
INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [PersonCategory], [HireDate], [EnrollmentDate], [Picture]) VALUES (25, N'Kapoor', N'Candace', 2, CAST(0x0000902800000000 AS DateTime), NULL, NULL)
INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [PersonCategory], [HireDate], [EnrollmentDate], [Picture]) VALUES (26, N'Rogers', N'Cody', 1, NULL, CAST(0x0000927A00000000 AS DateTime), NULL)
INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [PersonCategory], [HireDate], [EnrollmentDate], [Picture]) VALUES (27, N'Serrano', N'Stacy', 2, CAST(0x00008DD600000000 AS DateTime), NULL, NULL)
INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [PersonCategory], [HireDate], [EnrollmentDate], [Picture]) VALUES (28, N'White', N'Anthony', 1, NULL, CAST(0x0000910D00000000 AS DateTime), NULL)
INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [PersonCategory], [HireDate], [EnrollmentDate], [Picture]) VALUES (29, N'Griffin', N'Rachel', 1, NULL, CAST(0x0000955500000000 AS DateTime), NULL)
INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [PersonCategory], [HireDate], [EnrollmentDate], [Picture]) VALUES (30, N'Shan', N'Alicia', 1, NULL, CAST(0x000093E700000000 AS DateTime), NULL)
INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [PersonCategory], [HireDate], [EnrollmentDate], [Picture]) VALUES (31, N'Stewart', N'Jasmine', 2, CAST(0x00008B8100000000 AS DateTime), NULL, NULL)
INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [PersonCategory], [HireDate], [EnrollmentDate], [Picture]) VALUES (32, N'Xu', N'Kristen', 2, CAST(0x000090E500000000 AS DateTime), NULL, NULL)
INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [PersonCategory], [HireDate], [EnrollmentDate], [Picture]) VALUES (33, N'Gao', N'Erica', 1, NULL, CAST(0x0000931100000000 AS DateTime), NULL)
INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [PersonCategory], [HireDate], [EnrollmentDate], [Picture]) VALUES (34, N'Van Houten', N'Roger', 2, CAST(0x0000900100000000 AS DateTime), NULL, NULL)
SET IDENTITY_INSERT [dbo].[Person] OFF
/****** Object:  Table [dbo].[Department]     ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Department](
	[DepartmentID] [int] NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
	[Budget] [money] NOT NULL,
	[StartDate] [datetime] NOT NULL,
	[Administrator] [int] NULL,
 CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED 
(
	[DepartmentID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[Department] ([DepartmentID], [Name], [Budget], [StartDate], [Administrator]) VALUES (1, N'Engineering', 350000.0000, CAST(0x0000999C00000000 AS DateTime), 2)
INSERT [dbo].[Department] ([DepartmentID], [Name], [Budget], [StartDate], [Administrator]) VALUES (2, N'English', 120000.0000, CAST(0x0000999C00000000 AS DateTime), 6)
INSERT [dbo].[Department] ([DepartmentID], [Name], [Budget], [StartDate], [Administrator]) VALUES (4, N'Economics', 200000.0000, CAST(0x0000999C00000000 AS DateTime), 4)
INSERT [dbo].[Department] ([DepartmentID], [Name], [Budget], [StartDate], [Administrator]) VALUES (7, N'Mathematics', 250000.0000, CAST(0x0000999C00000000 AS DateTime), 3)
/****** Object:  Table [dbo].[OfficeAssignment]     ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[OfficeAssignment](
	[InstructorID] [int] NOT NULL,
	[Location] [nvarchar](50) NOT NULL,
	[Timestamp] [timestamp] NOT NULL,
 CONSTRAINT [PK_OfficeAssignment] PRIMARY KEY CLUSTERED 
(
	[InstructorID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[OfficeAssignment] ([InstructorID], [Location]) VALUES (1, N'17 Smith')
INSERT [dbo].[OfficeAssignment] ([InstructorID], [Location]) VALUES (4, N'29 Adams')
INSERT [dbo].[OfficeAssignment] ([InstructorID], [Location]) VALUES (5, N'37 Williams')
INSERT [dbo].[OfficeAssignment] ([InstructorID], [Location]) VALUES (18, N'143 Smith')
INSERT [dbo].[OfficeAssignment] ([InstructorID], [Location]) VALUES (25, N'57 Adams')
INSERT [dbo].[OfficeAssignment] ([InstructorID], [Location]) VALUES (27, N'271 Williams')
INSERT [dbo].[OfficeAssignment] ([InstructorID], [Location]) VALUES (31, N'131 Smith')
INSERT [dbo].[OfficeAssignment] ([InstructorID], [Location]) VALUES (32, N'203 Williams')
INSERT [dbo].[OfficeAssignment] ([InstructorID], [Location]) VALUES (34, N'213 Smith')
/****** Object:  StoredProcedure [dbo].[InsertPerson]     ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[InsertPerson]
		@LastName nvarchar(50),
		@FirstName nvarchar(50),
		@HireDate datetime,
		@EnrollmentDate datetime
		AS
		INSERT INTO dbo.Person (LastName, 
					FirstName, 
					HireDate, 
					EnrollmentDate)
		VALUES (@LastName, 
			@FirstName, 
			@HireDate, 
			@EnrollmentDate);
		SELECT SCOPE_IDENTITY() as NewPersonID;
GO
/****** Object:  StoredProcedure [dbo].[UpdatePerson]     ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[UpdatePerson]
		@PersonID int,
		@LastName nvarchar(50),
		@FirstName nvarchar(50),
		@HireDate datetime,
		@EnrollmentDate datetime
		AS
		UPDATE Person SET LastName=@LastName, 
				FirstName=@FirstName,
				HireDate=@HireDate,
				EnrollmentDate=@EnrollmentDate
		WHERE PersonID=@PersonID;
GO
/****** Object:  Table [dbo].[Course]     ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Course](
	[CourseID] [int] NOT NULL,
	[Title] [nvarchar](100) NOT NULL,
	[Credits] [int] NOT NULL,
	[DepartmentID] [int] NOT NULL,
 CONSTRAINT [PK_School.Course] PRIMARY KEY CLUSTERED 
(
	[CourseID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[Course] ([CourseID], [Title], [Credits], [DepartmentID]) VALUES (1045, N'Calculus', 4, 7)
INSERT [dbo].[Course] ([CourseID], [Title], [Credits], [DepartmentID]) VALUES (1050, N'Chemistry', 4, 1)
INSERT [dbo].[Course] ([CourseID], [Title], [Credits], [DepartmentID]) VALUES (1061, N'Physics', 4, 1)
INSERT [dbo].[Course] ([CourseID], [Title], [Credits], [DepartmentID]) VALUES (2021, N'Composition', 3, 2)
INSERT [dbo].[Course] ([CourseID], [Title], [Credits], [DepartmentID]) VALUES (2030, N'Poetry', 2, 2)
INSERT [dbo].[Course] ([CourseID], [Title], [Credits], [DepartmentID]) VALUES (2042, N'Literature', 4, 2)
INSERT [dbo].[Course] ([CourseID], [Title], [Credits], [DepartmentID]) VALUES (3141, N'Trigonometry', 4, 7)
INSERT [dbo].[Course] ([CourseID], [Title], [Credits], [DepartmentID]) VALUES (4022, N'Microeconomics', 3, 4)
INSERT [dbo].[Course] ([CourseID], [Title], [Credits], [DepartmentID]) VALUES (4041, N'Macroeconomics', 3, 4)
INSERT [dbo].[Course] ([CourseID], [Title], [Credits], [DepartmentID]) VALUES (4061, N'Quantitative', 2, 4)
/****** Object:  StoredProcedure [dbo].[DeletePerson]     ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[DeletePerson]
		@PersonID int
		AS
		DELETE FROM Person WHERE PersonID = @PersonID;
GO
/****** Object:  Table [dbo].[OnsiteCourse]     ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[OnsiteCourse](
	[CourseID] [int] NOT NULL,
	[Location] [nvarchar](50) NOT NULL,
	[Days] [nvarchar](50) NOT NULL,
	[Time] [smalldatetime] NOT NULL,
 CONSTRAINT [PK_OnsiteCourse] PRIMARY KEY CLUSTERED 
(
	[CourseID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[OnsiteCourse] ([CourseID], [Location], [Days], [Time]) VALUES (1045, N'121 Smith', N'MWHF', CAST(0x000003A2 AS SmallDateTime))
INSERT [dbo].[OnsiteCourse] ([CourseID], [Location], [Days], [Time]) VALUES (1050, N'123 Smith', N'MTWH', CAST(0x000002B2 AS SmallDateTime))
INSERT [dbo].[OnsiteCourse] ([CourseID], [Location], [Days], [Time]) VALUES (1061, N'234 Smith', N'TWHF', CAST(0x0000031B AS SmallDateTime))
INSERT [dbo].[OnsiteCourse] ([CourseID], [Location], [Days], [Time]) VALUES (2042, N'225 Adams', N'MTWH', CAST(0x00000294 AS SmallDateTime))
INSERT [dbo].[OnsiteCourse] ([CourseID], [Location], [Days], [Time]) VALUES (4022, N'23 Williams', N'MWF', CAST(0x0000021C AS SmallDateTime))
INSERT [dbo].[OnsiteCourse] ([CourseID], [Location], [Days], [Time]) VALUES (4061, N'22 Williams', N'TH', CAST(0x000002A3 AS SmallDateTime))
/****** Object:  Table [dbo].[OnlineCourse]     ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[OnlineCourse](
	[CourseID] [int] NOT NULL,
	[URL] [nvarchar](100) NOT NULL,
 CONSTRAINT [PK_OnlineCourse] PRIMARY KEY CLUSTERED 
(
	[CourseID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[OnlineCourse] ([CourseID], [URL]) VALUES (2021, N'http://www.fineartschool.net/Composition')
INSERT [dbo].[OnlineCourse] ([CourseID], [URL]) VALUES (2030, N'http://www.fineartschool.net/Poetry')
INSERT [dbo].[OnlineCourse] ([CourseID], [URL]) VALUES (3141, N'http://www.fineartschool.net/Trigonometry')
INSERT [dbo].[OnlineCourse] ([CourseID], [URL]) VALUES (4041, N'http://www.fineartschool.net/Macroeconomics')
/****** Object:  StoredProcedure [dbo].[DeleteOfficeAssignment]    ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[DeleteOfficeAssignment]
		@InstructorID int
		AS
		DELETE FROM OfficeAssignment
		WHERE InstructorID=@InstructorID;
GO
/****** Object:  Table [dbo].[CourseInstructor]     ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CourseInstructor](
	[CourseID] [int] NOT NULL,
	[PersonID] [int] NOT NULL,
 CONSTRAINT [PK_CourseInstructor] PRIMARY KEY CLUSTERED 
(
	[CourseID] ASC,
	[PersonID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[CourseInstructor] ([CourseID], [PersonID]) VALUES (1045, 5)
INSERT [dbo].[CourseInstructor] ([CourseID], [PersonID]) VALUES (1050, 1)
INSERT [dbo].[CourseInstructor] ([CourseID], [PersonID]) VALUES (1061, 31)
INSERT [dbo].[CourseInstructor] ([CourseID], [PersonID]) VALUES (2021, 27)
INSERT [dbo].[CourseInstructor] ([CourseID], [PersonID]) VALUES (2030, 4)
INSERT [dbo].[CourseInstructor] ([CourseID], [PersonID]) VALUES (2042, 25)
INSERT [dbo].[CourseInstructor] ([CourseID], [PersonID]) VALUES (4022, 18)
INSERT [dbo].[CourseInstructor] ([CourseID], [PersonID]) VALUES (4041, 32)
INSERT [dbo].[CourseInstructor] ([CourseID], [PersonID]) VALUES (4061, 34)
/****** Object:  Table [dbo].[CourseGrade]     ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CourseGrade](
	[EnrollmentID] [int] IDENTITY(1,1) NOT NULL,
	[CourseID] [int] NOT NULL,
	[StudentID] [int] NOT NULL,
	[Grade] [decimal](3, 2) NULL,
 CONSTRAINT [PK_CourseGrade] PRIMARY KEY CLUSTERED 
(
	[EnrollmentID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[CourseGrade] ON
INSERT [dbo].[CourseGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (1, 2021, 2, CAST(4.00 AS Decimal(3, 2)))
INSERT [dbo].[CourseGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (2, 2030, 2, CAST(3.50 AS Decimal(3, 2)))
INSERT [dbo].[CourseGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (3, 2021, 3, CAST(3.00 AS Decimal(3, 2)))
INSERT [dbo].[CourseGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (4, 2030, 3, CAST(4.00 AS Decimal(3, 2)))
INSERT [dbo].[CourseGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (5, 2021, 6, CAST(2.50 AS Decimal(3, 2)))
INSERT [dbo].[CourseGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (6, 2042, 6, CAST(3.50 AS Decimal(3, 2)))
INSERT [dbo].[CourseGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (7, 2021, 7, CAST(3.50 AS Decimal(3, 2)))
INSERT [dbo].[CourseGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (8, 2042, 7, CAST(4.00 AS Decimal(3, 2)))
INSERT [dbo].[CourseGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (9, 2021, 8, CAST(3.00 AS Decimal(3, 2)))
INSERT [dbo].[CourseGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (10, 2042, 8, CAST(3.00 AS Decimal(3, 2)))
INSERT [dbo].[CourseGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (11, 4041, 9, CAST(3.50 AS Decimal(3, 2)))
INSERT [dbo].[CourseGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (12, 4041, 10, NULL)
INSERT [dbo].[CourseGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (13, 4041, 11, CAST(2.50 AS Decimal(3, 2)))
INSERT [dbo].[CourseGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (14, 4041, 12, NULL)
INSERT [dbo].[CourseGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (15, 4061, 12, NULL)
INSERT [dbo].[CourseGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (16, 4022, 14, CAST(3.00 AS Decimal(3, 2)))
INSERT [dbo].[CourseGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (17, 4022, 13, CAST(4.00 AS Decimal(3, 2)))
INSERT [dbo].[CourseGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (18, 4061, 13, CAST(4.00 AS Decimal(3, 2)))
INSERT [dbo].[CourseGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (19, 4041, 14, CAST(3.00 AS Decimal(3, 2)))
INSERT [dbo].[CourseGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (20, 4022, 15, CAST(2.50 AS Decimal(3, 2)))
INSERT [dbo].[CourseGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (21, 4022, 16, CAST(2.00 AS Decimal(3, 2)))
INSERT [dbo].[CourseGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (22, 4022, 17, NULL)
INSERT [dbo].[CourseGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (23, 4022, 19, CAST(3.50 AS Decimal(3, 2)))
INSERT [dbo].[CourseGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (24, 4061, 20, CAST(4.00 AS Decimal(3, 2)))
INSERT [dbo].[CourseGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (25, 4061, 21, CAST(2.00 AS Decimal(3, 2)))
INSERT [dbo].[CourseGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (26, 4022, 22, CAST(3.00 AS Decimal(3, 2)))
INSERT [dbo].[CourseGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (27, 4041, 22, CAST(3.50 AS Decimal(3, 2)))
INSERT [dbo].[CourseGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (28, 4061, 22, CAST(2.50 AS Decimal(3, 2)))
INSERT [dbo].[CourseGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (29, 4022, 23, CAST(3.00 AS Decimal(3, 2)))
INSERT [dbo].[CourseGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (30, 1045, 23, CAST(1.50 AS Decimal(3, 2)))
INSERT [dbo].[CourseGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (31, 1061, 24, CAST(4.00 AS Decimal(3, 2)))
INSERT [dbo].[CourseGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (32, 1061, 25, CAST(3.00 AS Decimal(3, 2)))
INSERT [dbo].[CourseGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (33, 1050, 26, CAST(3.50 AS Decimal(3, 2)))
INSERT [dbo].[CourseGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (34, 1061, 26, CAST(3.00 AS Decimal(3, 2)))
INSERT [dbo].[CourseGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (35, 1061, 27, CAST(3.00 AS Decimal(3, 2)))
INSERT [dbo].[CourseGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (36, 1045, 28, CAST(2.50 AS Decimal(3, 2)))
INSERT [dbo].[CourseGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (37, 1050, 28, CAST(3.50 AS Decimal(3, 2)))
INSERT [dbo].[CourseGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (38, 1061, 29, CAST(4.00 AS Decimal(3, 2)))
INSERT [dbo].[CourseGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (39, 1050, 30, CAST(3.50 AS Decimal(3, 2)))
INSERT [dbo].[CourseGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (40, 1061, 30, CAST(4.00 AS Decimal(3, 2)))
SET IDENTITY_INSERT [dbo].[CourseGrade] OFF
/****** Object:  StoredProcedure [dbo].[UpdateOfficeAssignment]     ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[UpdateOfficeAssignment]
		@InstructorID int,
		@Location nvarchar(50),
		@OrigTimestamp timestamp
		AS
		UPDATE OfficeAssignment SET Location=@Location 
		WHERE InstructorID=@InstructorID AND [Timestamp]=@OrigTimestamp;
		IF @@ROWCOUNT > 0
		BEGIN
			SELECT [Timestamp] FROM OfficeAssignment 
				WHERE InstructorID=@InstructorID;
		END
GO
/****** Object:  StoredProcedure [dbo].[InsertOfficeAssignment]     ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[InsertOfficeAssignment]
		@InstructorID int,
		@Location nvarchar(50)
		AS
		INSERT INTO dbo.OfficeAssignment (InstructorID, Location)
		VALUES (@InstructorID, @Location);
		IF @@ROWCOUNT > 0
		BEGIN
			SELECT [Timestamp] FROM OfficeAssignment 
				WHERE InstructorID=@InstructorID;
		END
GO
/****** Object:  StoredProcedure [dbo].[GetStudentGrades]     ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetStudentGrades]
            @StudentID int
            AS
            SELECT EnrollmentID, Grade FROM dbo.CourseGrade
            WHERE StudentID = @StudentID
GO
/****** Object:  Default [DF_Person_PersonCategory]     ******/
ALTER TABLE [dbo].[Person] ADD  CONSTRAINT [DF_Person_PersonCategory]  DEFAULT ((1)) FOR [PersonCategory]
GO
/****** Object:  ForeignKey [FK_Course_Department]    7 ******/
ALTER TABLE [dbo].[Course]  WITH CHECK ADD  CONSTRAINT [FK_Course_Department] FOREIGN KEY([DepartmentID])
REFERENCES [dbo].[Department] ([DepartmentID])
GO
ALTER TABLE [dbo].[Course] CHECK CONSTRAINT [FK_Course_Department]
GO
/****** Object:  ForeignKey [FK_CourseGrade_Course]     ******/
ALTER TABLE [dbo].[CourseGrade]  WITH CHECK ADD  CONSTRAINT [FK_CourseGrade_Course] FOREIGN KEY([CourseID])
REFERENCES [dbo].[Course] ([CourseID])
GO
ALTER TABLE [dbo].[CourseGrade] CHECK CONSTRAINT [FK_CourseGrade_Course]
GO
/****** Object:  ForeignKey [FK_CourseGrade_Student]     ******/
ALTER TABLE [dbo].[CourseGrade]  WITH CHECK ADD  CONSTRAINT [FK_CourseGrade_Student] FOREIGN KEY([StudentID])
REFERENCES [dbo].[Person] ([PersonID])
GO
ALTER TABLE [dbo].[CourseGrade] CHECK CONSTRAINT [FK_CourseGrade_Student]
GO
/****** Object:  ForeignKey [FK_CourseInstructor_Course]    7 ******/
ALTER TABLE [dbo].[CourseInstructor]  WITH CHECK ADD  CONSTRAINT [FK_CourseInstructor_Course] FOREIGN KEY([CourseID])
REFERENCES [dbo].[Course] ([CourseID])
GO
ALTER TABLE [dbo].[CourseInstructor] CHECK CONSTRAINT [FK_CourseInstructor_Course]
GO
/****** Object:  ForeignKey [FK_CourseInstructor_Person]     ******/
ALTER TABLE [dbo].[CourseInstructor]  WITH CHECK ADD  CONSTRAINT [FK_CourseInstructor_Person] FOREIGN KEY([PersonID])
REFERENCES [dbo].[Person] ([PersonID])
GO
ALTER TABLE [dbo].[CourseInstructor] CHECK CONSTRAINT [FK_CourseInstructor_Person]
GO
/****** Object:  ForeignKey [FK_OfficeAssignment_Person]     ******/
ALTER TABLE [dbo].[OfficeAssignment]  WITH CHECK ADD  CONSTRAINT [FK_OfficeAssignment_Person] FOREIGN KEY([InstructorID])
REFERENCES [dbo].[Person] ([PersonID])
GO
ALTER TABLE [dbo].[OfficeAssignment] CHECK CONSTRAINT [FK_OfficeAssignment_Person]
GO
/****** Object:  ForeignKey [FK_OnlineCourse_Course]     ******/
ALTER TABLE [dbo].[OnlineCourse]  WITH CHECK ADD  CONSTRAINT [FK_OnlineCourse_Course] FOREIGN KEY([CourseID])
REFERENCES [dbo].[Course] ([CourseID])
GO
ALTER TABLE [dbo].[OnlineCourse] CHECK CONSTRAINT [FK_OnlineCourse_Course]
GO
/****** Object:  ForeignKey [FK_OnsiteCourse_Course]     ******/
ALTER TABLE [dbo].[OnsiteCourse]  WITH CHECK ADD  CONSTRAINT [FK_OnsiteCourse_Course] FOREIGN KEY([CourseID])
REFERENCES [dbo].[Course] ([CourseID])
GO
ALTER TABLE [dbo].[OnsiteCourse] CHECK CONSTRAINT [FK_OnsiteCourse_Course]
GO
